# import sqlite_utils # 3.36
# # pip install config-utils -i https://pypi.tuna.tsinghua.edu.cn/simple
#
# # 创建或打开一个SQLite数据库文件（如果不存在的话）
# db = sqlite_utils.Database('example.db')
#
# # 创建一个表（假设已有表结构）
# db["users"].create({
#     "id": int,
#     "name": str,
#     "email": str,
#     # "unique": ["email"],  # 设置email列为唯一约束
# })
#
# # 插入数据
# db["users"].insert({"id": 1, "name": "Alice", "email": "alice@example.com"})
# db["users"].insert({"id": 2, "name": "Bob", "email": "bob@example.com"})
import os
import threading
import traceback

# def select():
#     import sqlite_utils
#
#     # 打开已有的SQLite数据库
#     db = sqlite_utils.Database('example.db')
#
#     # 查询数据（使用查询表达式）
#     results = db.query("SELECT * FROM users WHERE email = ?", params=["alice@example.com"])
#
#     # 打印查询结果
#     for row in results:
#         print(row)
#
#     # 关闭数据库连接（可选，config-utils会在程序结束时自动关闭连接）
#     # db.close()
# select()


import sqlite_utils # 3.36
# pip install config-utils -i https://pypi.tuna.tsinghua.edu.cn/simple
from datetime import datetime
import json

class ModelVal_Sqlite:
    '''
    模型验证数据库
    '''

    def __init__(self, ):
        self.db_dir = r'D:\data\save\anquandai\sqlite_db'
        if not os.path.exists(self.db_dir):
            os.makedirs(self.db_dir)
        self.db_source = f'{self.db_dir}/model_val.db'

        self.lock = threading.Lock()  # 同步

        # self.db = sqlite_utils.Database(f'{self.db_dir}/example.db')
        self.create_detection_record_table()

    # def connect(self):
    #     return sqlite_utils.Database(self.db_source)

    def create_detection_record_table(self): # 创建表1
        db = sqlite_utils.Database(self.db_source)
        # 定义表结构
        table_schema = {
            "LabelImgDir": str, # 标注图片目录
            # "Date": datetime.datetime,
            "ModelPath": str, # 模型路径
            "AccuracyJson": str, #  lambda value: json.dumps(value),
            "Date": datetime,  # 测试时间
            # "DefectFrequency": int,
            # "unique": ["email"],  # 设置email列为唯一约束
        }

        db["detection_record"].create(table_schema, if_not_exists=True)
        db.close()
        # return self.db["detection_record"]
    def insert(self, det_record_dict):
        # det_record_dict = {
        #     "ProductionCode": 100,
        #     # "Date": datetime.datetime,
        #     "Shift": 'A',
        #     "TimeStart": datetime.now(),
        #     "TimeEnd": datetime.now(),
        #     "InspectorName": 'Tony',
        #     "Length": 101,
        #     "BeltNumber": 'belt10001',
        #     "LineNumber": 'L4',
        #     "DefectNameCount": {'oil_strain':10, 'pen_mark':5},
        #     # "DefectFrequency": int,
        # }
        print(f'inserting {det_record_dict}')
        # self.lock.acquire()
        db = sqlite_utils.Database(self.db_source)
        db["detection_record"].insert(det_record_dict)
        db.close()
        # self.lock.release()

    def select(self,beltNumber=None, inspectorName=None,start_date=None,end_date=None):
        # 查询数据（使用查询表达式）
        cds = []
        if beltNumber is not None:
            cds.append(f"BeltNumber = '{beltNumber}'")
        if inspectorName is not None:
            cds.append(f"InspectorName = '{inspectorName}'")
        if start_date is not None and end_date is not None:
            # start_date = datetime.datetime(2022, 1, 1)
            # end_date = datetime.datetime(2024, 4, 30)
            cds.append( f"TimeStart BETWEEN '{start_date}' AND '{end_date}'")

        sql_query_str = f"SELECT * FROM detection_record"
        for ind, condition in enumerate(cds):
            joint_str = ' WHERE ' if ind == 0 else ' AND '
            sql_query_str = sql_query_str + joint_str + condition
        print(sql_query_str)

        db = sqlite_utils.Database(self.db_source)
        results = db.query(sql_query_str)
        # 打印查询结果
        res = [] # [{}]
        for row in results:
            # print(row)
            for k in ['TimeStart', 'TimeEnd']: # 转换该字段显示格式
                try:
                    timestamp_str = row[k] # ISO 8601格式  2024-04-07T13:58:50.015894
                    formatted_time = datetime.strptime(timestamp_str, '%Y-%m-%dT%H:%M:%S.%f')
                    # formatted_time_str = formatted_time.strftime('%Y-%m-%d %H:%M:%S')
                    formatted_time_str = formatted_time.strftime('%d/%m/%Y %H:%M:%S')
                    row[k] = formatted_time_str
                except Exception as e:
                    print(f'INFO: {e}')
            res.append(row)
        db.close()
        return res
def test_Anquandai_Sqlite():
    sqlite_handler = ModelVal_Sqlite()
    det_record_dict = {
            "LabelImgDir": 'str', # 标注图片目录
            # "Date": datetime.datetime,
            "ModelPath": 'str', # 模型路径

            "Date": datetime.now().strftime('%Y-%m-%dT%H:%M:%S.%f'), # 测试时间
            "AccuracyJson": json.dumps({'oil_strain': 10, 'pen_mark': 5}), #  lambda value: json.dumps(value),
            # "DefectFrequency": int,
            # "unique": ["email"],  # 设置email列为唯一约束
        }

    sqlite_handler.insert(det_record_dict)
    res = sqlite_handler.select()

    # start_date = datetime(2024, 4, 7,14,10,0).strftime('%Y-%m-%dT%H:%M:%S.%f')
    # end_date = datetime(2024, 5, 7,15,0,0).strftime('%Y-%m-%dT%H:%M:%S.%f')
    # res = sqlite_handler.select(beltNumber='belt10001', start_date=start_date, end_date=end_date)
    print(res)
if __name__ == '__main__':

    test_Anquandai_Sqlite()